from flask import Flask, jsonify, request, make_response
from flask_cors import CORS
import mysql.connector
import json
import os

app = Flask(__name__)

# 更简化的CORS配置
CORS(app)

# 数据库连接配置
def get_db_connection():
    try:
        conn = mysql.connector.connect(
            host=os.getenv('MYSQL_HOST', 'localhost'),
            user=os.getenv('MYSQL_USER', 'root'),
            password=os.getenv('MYSQL_PASSWORD', 'neo..6555894'),
            database=os.getenv('MYSQL_DATABASE', 'sales_chat_mvp')
        )
        return conn
    except Exception as e:
        print(f"数据库连接错误: {e}")
        return None

# 添加调试路由，不依赖数据库
@app.route('/', methods=['GET'])
def index():
    return jsonify({"message": "Flask API is running!"})

@app.route('/api/test-cors', methods=['GET'])
def test_cors():
    return jsonify({"message": "CORS is working!"})

# 测试MySQL连接
@app.route('/api/test-db', methods=['GET'])
def test_db():
    conn = get_db_connection()
    if not conn:
        return jsonify({"error": "无法连接到数据库"}), 500
        
    cursor = conn.cursor(dictionary=True)
    
    try:
        # 获取数据库版本信息
        cursor.execute("SELECT VERSION() as version")
        result = cursor.fetchone()
        
        # 获取数据库表信息
        cursor.execute("SHOW TABLES")
        tables = [table['Tables_in_sales_chat_mvp'] for table in cursor.fetchall()]
        
        return jsonify({
            "status": "成功",
            "message": "数据库连接正常",
            "version": result['version'],
            "database": "sales_chat_mvp",
            "tables": tables
        })
    
    except Exception as e:
        return jsonify({"error": f"数据库查询错误: {str(e)}"}), 500
    
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

# 添加错误处理
@app.errorhandler(403)
def forbidden(e):
    return jsonify({"error": "403 Forbidden", "message": str(e)}), 403

@app.errorhandler(500)
def server_error(e):
    return jsonify({"error": "500 Internal Server Error", "message": str(e)}), 500

# 获取所有有效的客户数据
@app.route('/api/customers', methods=['GET'])
def get_customers():
    conn = get_db_connection()
    if not conn:
        return jsonify({"error": "无法连接到数据库"}), 500
        
    cursor = conn.cursor(dictionary=True)
    
    try:
        query = """
        SELECT 
            id, 
            nickname, 
            customer_nickname, 
            ai_judgment, 
            ai_tags
        FROM 
            chat_history 
        WHERE 
            ai_judgment IS NOT NULL
        """
        
        cursor.execute(query)
        records = cursor.fetchall()
        
        # 使用字典来存储去重后的客户数据
        unique_customers = {}
        
        for record in records:
            try:
                # 解析AI判断结果
                if record['ai_judgment']:
                    judgment = json.loads(record['ai_judgment'])
                    
                    # 只保留核心业务流程分析的记录
                    if judgment.get('判断结果') == "核心业务流程分析":
                        customer_nickname = record['customer_nickname'] or record['nickname']
                        
                        # 如果该昵称的客户已存在，则跳过
                        if customer_nickname in unique_customers:
                            continue
                            
                        customer = {
                            'id': record['id'],
                            'nickname': customer_nickname,
                            'sales_stage': judgment.get('当前销售阶段', '未知'),
                            'platform_tags': []
                        }
                        
                        # 解析标签
                        if record['ai_tags']:
                            tags = json.loads(record['ai_tags'])
                            if '标签' in tags and '平台标签' in tags['标签']:
                                platform_tags = tags['标签']['平台标签']
                                customer['platform_tags'] = platform_tags if platform_tags else ['未知']
                            else:
                                customer['platform_tags'] = ['未知']
                        else:
                            customer['platform_tags'] = ['未知']
                            
                        # 将客户添加到去重字典中
                        unique_customers[customer_nickname] = customer
            except Exception as e:
                print(f"解析记录 {record['id']} 时出错: {e}")
                continue
        
        # 将字典值转换为列表返回
        return jsonify(list(unique_customers.values()))
    
    except Exception as e:
        print(f"查询数据库时出错: {e}")
        return jsonify({'error': str(e)}), 500
    
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

# 获取销售阶段统计数据
@app.route('/api/stats/sales-stages', methods=['GET'])
def get_sales_stages_stats():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        query = """
        SELECT 
            nickname,
            customer_nickname,
            ai_judgment
        FROM 
            chat_history 
        WHERE 
            ai_judgment IS NOT NULL
        """
        
        cursor.execute(query)
        records = cursor.fetchall()
        
        # 使用字典来存储去重后的客户数据
        unique_customers = {}
        
        for record in records:
            try:
                if record['ai_judgment']:
                    judgment = json.loads(record['ai_judgment'])
                    
                    if judgment.get('判断结果') == "核心业务流程分析":
                        customer_nickname = record['customer_nickname'] or record['nickname']
                        stage = judgment.get('当前销售阶段', '未知')
                        
                        # 如果该昵称的客户已存在，则跳过
                        if customer_nickname in unique_customers:
                            continue
                            
                        unique_customers[customer_nickname] = stage
            except Exception as e:
                print(f"解析记录时出错: {e}")
                continue
        
        # 统计各销售阶段的数量
        stage_counts = {}
        for stage in unique_customers.values():
            stage_counts[stage] = stage_counts.get(stage, 0) + 1
        
        # 转换为前端所需格式
        result = [{'name': stage, 'value': count} for stage, count in stage_counts.items()]
        return jsonify(result)
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()

# 获取平台标签统计数据
@app.route('/api/stats/platforms', methods=['GET'])
def get_platform_stats():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        query = """
        SELECT 
            nickname,
            customer_nickname,
            ai_judgment,
            ai_tags
        FROM 
            chat_history 
        WHERE 
            ai_judgment IS NOT NULL
        """
        
        cursor.execute(query)
        records = cursor.fetchall()
        
        # 使用字典来存储去重后的客户数据
        unique_customers = {}
        
        for record in records:
            try:
                if record['ai_judgment']:
                    judgment = json.loads(record['ai_judgment'])
                    
                    if judgment.get('判断结果') == "核心业务流程分析":
                        customer_nickname = record['customer_nickname'] or record['nickname']
                        
                        # 如果该昵称的客户已存在，则跳过
                        if customer_nickname in unique_customers:
                            continue
                            
                        platforms = []
                        if record['ai_tags']:
                            tags = json.loads(record['ai_tags'])
                            if '标签' in tags and '平台标签' in tags['标签']:
                                platforms = tags['标签']['平台标签']
                        
                        if not platforms:
                            platforms = ['未知']
                            
                        unique_customers[customer_nickname] = platforms
            except Exception as e:
                print(f"解析记录时出错: {e}")
                continue
        
        # 统计各平台的数量
        platform_counts = {}
        for platforms in unique_customers.values():
            for platform in platforms:
                platform_counts[platform] = platform_counts.get(platform, 0) + 1
        
        # 转换为前端所需格式
        result = [{'name': platform, 'value': count} for platform, count in platform_counts.items()]
        return jsonify(result)
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()

# 根据销售阶段或平台获取客户列表
@app.route('/api/customers/filter', methods=['GET'])
def get_filtered_customers():
    filter_type = request.args.get('type')  # 'sales_stage' 或 'platform'
    filter_value = request.args.get('value')
    
    if not filter_type or not filter_value:
        return jsonify({'error': '缺少必要的过滤参数'}), 400
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        query = """
        SELECT 
            id, 
            nickname, 
            customer_nickname, 
            ai_judgment, 
            ai_tags,
            ai_customer_profile,
            ai_communication_quality,
            chat_history,
            week_start_time,
            time_span
        FROM 
            chat_history 
        WHERE 
            ai_judgment IS NOT NULL
        """
        
        cursor.execute(query)
        records = cursor.fetchall()
        
        # 使用字典来存储去重后的客户数据
        unique_customers = {}
        
        for record in records:
            try:
                if record['ai_judgment']:
                    judgment = json.loads(record['ai_judgment'])
                    
                    if judgment.get('判断结果') == "核心业务流程分析":
                        customer_nickname = record['customer_nickname'] or record['nickname']
                        
                        # 根据销售阶段过滤
                        if filter_type == 'sales_stage':
                            stage = judgment.get('当前销售阶段', '未知')
                            if stage == filter_value:
                                process_filtered_customer(record, unique_customers, customer_nickname)
                        
                        # 根据平台标签过滤
                        elif filter_type == 'platform':
                            if record['ai_tags']:
                                tags = json.loads(record['ai_tags'])
                                if '标签' in tags and '平台标签' in tags['标签']:
                                    platforms = tags['标签']['平台标签']
                                    if not platforms and filter_value == '未知':
                                        process_filtered_customer(record, unique_customers, customer_nickname)
                                    elif filter_value in platforms:
                                        process_filtered_customer(record, unique_customers, customer_nickname)
                                elif filter_value == '未知':
                                    process_filtered_customer(record, unique_customers, customer_nickname)
                            elif filter_value == '未知':
                                process_filtered_customer(record, unique_customers, customer_nickname)
            except Exception as e:
                print(f"解析记录 {record['id']} 时出错: {e}")
                continue
        
        # 将字典值转换为列表，并添加序号
        filtered_customers = []
        for index, customer in enumerate(unique_customers.values(), 1):
            customer['index'] = index
            filtered_customers.append(customer)
            
        return jsonify(filtered_customers)
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()

# 根据多个销售阶段获取客户列表
@app.route('/api/customers/filter-stages', methods=['POST'])
def get_customers_by_stages():
    if not request.is_json:
        return jsonify({'error': '请求必须是JSON格式'}), 400
    
    data = request.get_json()
    stages = data.get('stages', [])
    
    if not stages or not isinstance(stages, list):
        return jsonify({'error': '缺少必要的销售阶段参数或格式不正确'}), 400
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        query = """
        SELECT 
            id, 
            nickname, 
            customer_nickname, 
            ai_judgment, 
            ai_tags,
            ai_customer_profile,
            ai_communication_quality,
            chat_history,
            week_start_time,
            time_span
        FROM 
            chat_history 
        WHERE 
            ai_judgment IS NOT NULL
        """
        
        cursor.execute(query)
        records = cursor.fetchall()
        
        # 使用字典来存储去重后的客户数据
        unique_customers = {}
        
        for record in records:
            try:
                if record['ai_judgment']:
                    judgment = json.loads(record['ai_judgment'])
                    
                    if judgment.get('判断结果') == "核心业务流程分析":
                        customer_nickname = record['customer_nickname'] or record['nickname']
                        stage = judgment.get('当前销售阶段', '未知')
                        
                        # 检查当前记录的销售阶段是否在请求的阶段列表中
                        if stage in stages:
                            # 处理客户数据
                            process_filtered_customer(record, unique_customers, customer_nickname)
            except Exception as e:
                print(f"解析记录 {record['id']} 时出错: {e}")
                continue
        
        # 将字典值转换为列表，并添加序号
        filtered_customers = []
        for index, customer in enumerate(unique_customers.values(), 1):
            customer['index'] = index
            filtered_customers.append(customer)
            
        return jsonify(filtered_customers)
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()

# 处理客户数据的辅助函数
def process_customer_data(record, index):
    customer_data = {
        'index': index,
        'id': record['id'],
        'nickname': record['customer_nickname'] or record['nickname'],
        'profile': '未提供',
        'needs': [],
        'tags': [],
        'communication_quality': {
            'strengths': [],
            'improvements': []
        },
        'chat_time': [],
        'chat_history': []
    }
    
    # 处理客户画像
    if record['ai_customer_profile']:
        try:
            profile_data = json.loads(record['ai_customer_profile'])
            if '客户画像完善' in profile_data and '画像总结' in profile_data['客户画像完善']:
                customer_data['profile'] = profile_data['客户画像完善']['画像总结']
            
            # 处理需求与痛点
            if '客户画像完善' in profile_data and '需求与痛点' in profile_data['客户画像完善']:
                needs = profile_data['客户画像完善']['需求与痛点']
                customer_data['needs'] = [need['描述'] for need in needs] if needs else []
        except Exception as e:
            print(f"解析客户画像数据出错: {e}")
    
    # 处理标签
    if record['ai_tags']:
        try:
            tags_data = json.loads(record['ai_tags'])
            if '标签' in tags_data:
                if '平台标签' in tags_data['标签']:
                    customer_data['tags'].extend(tags_data['标签']['平台标签'])
                if '是否成交' in tags_data['标签']:
                    customer_data['tags'].append(tags_data['标签']['是否成交'])
        except Exception as e:
            print(f"解析标签数据出错: {e}")
    
    # 处理沟通质量
    if record['ai_communication_quality']:
        try:
            comm_quality = json.loads(record['ai_communication_quality'])
            if '沟通质量打分' in comm_quality:
                quality_data = comm_quality['沟通质量打分']
                
                # 提取沟通优点
                if '沟通优点' in quality_data:
                    customer_data['communication_quality']['strengths'] = quality_data['沟通优点']
                
                # 提取需要改进的地方
                if '需要改进的地方' in quality_data:
                    customer_data['communication_quality']['improvements'] = quality_data['需要改进的地方']
        except Exception as e:
            print(f"解析沟通质量数据出错: {e}")
    
    # 处理沟通时间
    if record['time_span']:
        try:
            # 解析时间范围
            time_data = process_chat_time(record['time_span'])
            if time_data:
                customer_data['chat_time'].append(time_data)
        except Exception as e:
            print(f"解析沟通时间数据出错: {e}")
    
    # 处理聊天记录
    if record['chat_history']:
        try:
            chat_history = json.loads(record['chat_history'])
            customer_data['chat_history'] = chat_history
        except Exception as e:
            print(f"解析聊天记录出错: {e}")
    
    return customer_data

# 处理过滤后的客户数据
def process_filtered_customer(record, unique_customers, customer_nickname):
    # 如果客户已存在，则合并数据
    if customer_nickname in unique_customers:
        # 合并聊天记录
        if record['chat_history']:
            try:
                new_chat = json.loads(record['chat_history'])
                existing_chat = unique_customers[customer_nickname]['chat_history']
                # 合并聊天记录并去重
                merged_chat = existing_chat + [msg for msg in new_chat if msg not in existing_chat]
                unique_customers[customer_nickname]['chat_history'] = merged_chat
            except Exception as e:
                print(f"合并聊天记录出错: {e}")
                
        # 合并标签
        if record['ai_tags']:
            try:
                tags_data = json.loads(record['ai_tags'])
                if '标签' in tags_data:
                    # 合并平台标签
                    if '平台标签' in tags_data['标签']:
                        platform_tags = tags_data['标签']['平台标签']
                        existing_tags = unique_customers[customer_nickname]['tags']
                        # 添加不存在的标签
                        for tag in platform_tags:
                            if tag not in existing_tags:
                                existing_tags.append(tag)
                    
                    # 处理成交状态标签
                    if '是否成交' in tags_data['标签']:
                        transaction_status = tags_data['标签']['是否成交']
                        if transaction_status not in unique_customers[customer_nickname]['tags']:
                            unique_customers[customer_nickname]['tags'].append(transaction_status)
            except Exception as e:
                print(f"合并标签数据出错: {e}")
                
        # 合并客户画像和需求痛点
        if record['ai_customer_profile']:
            try:
                profile_data = json.loads(record['ai_customer_profile'])
                # 如果现有客户没有画像信息，但新记录有，则更新
                if unique_customers[customer_nickname]['profile'] == '未提供' and '客户画像完善' in profile_data and '画像总结' in profile_data['客户画像完善']:
                    unique_customers[customer_nickname]['profile'] = profile_data['客户画像完善']['画像总结']
                
                # 合并需求与痛点
                if '客户画像完善' in profile_data and '需求与痛点' in profile_data['客户画像完善']:
                    new_needs = profile_data['客户画像完善']['需求与痛点']
                    if new_needs:
                        existing_needs = unique_customers[customer_nickname]['needs']
                        new_need_descriptions = [need['描述'] for need in new_needs]
                        # 添加不存在的需求
                        for need_desc in new_need_descriptions:
                            if need_desc not in existing_needs:
                                existing_needs.append(need_desc)
            except Exception as e:
                print(f"合并客户画像数据出错: {e}")
                
        # 合并沟通质量
        if record['ai_communication_quality']:
            try:
                comm_quality = json.loads(record['ai_communication_quality'])
                if '沟通质量打分' in comm_quality:
                    quality_data = comm_quality['沟通质量打分']
                    
                    # 合并沟通优点
                    if '沟通优点' in quality_data:
                        new_strengths = quality_data['沟通优点']
                        existing_strengths = unique_customers[customer_nickname]['communication_quality']['strengths']
                        # 添加不存在的优点
                        for strength in new_strengths:
                            if strength not in existing_strengths:
                                existing_strengths.append(strength)
                    
                    # 合并需要改进的地方
                    if '需要改进的地方' in quality_data:
                        new_improvements = quality_data['需要改进的地方']
                        existing_improvements = unique_customers[customer_nickname]['communication_quality']['improvements']
                        # 添加不存在的改进点
                        for improvement in new_improvements:
                            if improvement not in existing_improvements:
                                existing_improvements.append(improvement)
            except Exception as e:
                print(f"合并沟通质量数据出错: {e}")
                
        # 合并沟通时间
        if record['time_span']:
            try:
                # 解析时间范围
                time_data = process_chat_time(record['time_span'])
                if time_data:
                    # 尝试合并时间范围
                    merge_chat_time(unique_customers[customer_nickname]['chat_time'], time_data)
            except Exception as e:
                print(f"合并沟通时间数据出错: {e}")
    else:
        # 如果客户不存在，则添加新客户
        customer_data = process_customer_data(record, 0)  # 序号稍后添加
        unique_customers[customer_nickname] = customer_data

# 处理聊天时间范围
def process_chat_time(time_span_str):
    try:
        if not time_span_str:
            return None
            
        time_span = json.loads(time_span_str)
        if 'start_time' in time_span and 'end_time' in time_span:
            # 只取日期部分
            start_date = time_span['start_time'].split(' ')[0]
            end_date = time_span['end_time'].split(' ')[0]
            
            return {
                'start': start_date,
                'end': end_date
            }
        return None
    except Exception as e:
        print(f"处理时间范围出错: {e}")
        return None

# 合并聊天时间范围
def merge_chat_time(existing_time_ranges, new_time_range):
    if not new_time_range:
        return
    
    # 检查是否可以合并到现有时间范围
    for i, time_range in enumerate(existing_time_ranges):
        # 检查是否连续
        if is_consecutive_dates(time_range['end'], new_time_range['start']):
            # 合并时间范围
            existing_time_ranges[i]['end'] = new_time_range['end']
            return
        elif is_consecutive_dates(new_time_range['end'], time_range['start']):
            # 合并时间范围
            existing_time_ranges[i]['start'] = new_time_range['start']
            return
        
        # 检查是否有重叠
        if (time_range['start'] <= new_time_range['end'] and 
            time_range['end'] >= new_time_range['start']):
            # 更新为更广泛的范围
            existing_time_ranges[i]['start'] = min(time_range['start'], new_time_range['start'])
            existing_time_ranges[i]['end'] = max(time_range['end'], new_time_range['end'])
            return
    
    # 如果无法合并，则添加新的时间范围
    existing_time_ranges.append(new_time_range)
    
    # 按开始日期排序
    existing_time_ranges.sort(key=lambda x: x['start'])

# 检查两个日期是否连续
def is_consecutive_dates(date1_str, date2_str):
    try:
        from datetime import datetime, timedelta
        
        date1 = datetime.strptime(date1_str, "%Y-%m-%d")
        date2 = datetime.strptime(date2_str, "%Y-%m-%d")
        
        # 检查日期2是否是日期1的下一天
        return (date2 - date1) == timedelta(days=1)
    except Exception as e:
        print(f"检查日期连续性出错: {e}")
        return False

# 根据关键词搜索客户
@app.route('/api/customers/search', methods=['GET'])
def search_customers():
    keyword = request.args.get('keyword', '')
    
    if not keyword:
        return jsonify({'error': '缺少搜索关键词'}), 400
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        # 模糊搜索的SQL查询
        query = """
        SELECT 
            id, 
            nickname, 
            customer_nickname, 
            ai_judgment, 
            ai_tags,
            ai_customer_profile,
            ai_communication_quality,
            chat_history,
            week_start_time,
            time_span
        FROM 
            chat_history 
        WHERE 
            ai_judgment IS NOT NULL
        """
        
        cursor.execute(query)
        records = cursor.fetchall()
        
        # 使用字典来存储去重后的客户数据
        unique_customers = {}
        
        # 将关键词转为小写，用于不区分大小写的比较
        keyword_lower = keyword.lower()
        
        for record in records:
            try:
                if record['ai_judgment']:
                    judgment = json.loads(record['ai_judgment'])
                    
                    if judgment.get('判断结果') == "核心业务流程分析":
                        # 获取客户昵称
                        customer_nickname = record['customer_nickname'] or record['nickname']
                        
                        # 只检查客户昵称是否包含关键词（不区分大小写）
                        if customer_nickname and keyword_lower in customer_nickname.lower():
                            process_filtered_customer(record, unique_customers, customer_nickname)
            except Exception as e:
                print(f"解析记录 {record['id']} 时出错: {e}")
                continue
        
        # 将字典值转换为列表，并添加序号
        filtered_customers = []
        for index, customer in enumerate(unique_customers.values(), 1):
            customer['index'] = index
            filtered_customers.append(customer)
            
        return jsonify(filtered_customers)
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    
    finally:
        cursor.close()
        conn.close()

# 获取每月新增客户数量统计
@app.route('/api/stats/monthly-customers', methods=['GET'])
def get_monthly_customers():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    try:
        query = """
        SELECT 
            nickname,
            customer_nickname,
            ai_judgment,
            week_start_time
        FROM 
            chat_history 
        WHERE 
            ai_judgment IS NOT NULL
            AND week_start_time IS NOT NULL
        """
        cursor.execute(query)
        records = cursor.fetchall()
        # 统计每个客户的最早week_start_time
        customer_earliest_time = {}
        for record in records:
            try:
                if record['ai_judgment']:
                    judgment = json.loads(record['ai_judgment'])
                    if judgment.get('判断结果') == "核心业务流程分析":
                        customer_nickname = record['customer_nickname'] or record['nickname']
                        week_time = record['week_start_time']
                        if not week_time:
                            continue
                        week_time = str(week_time)  # 兼容datetime和str
                        if (customer_nickname not in customer_earliest_time or 
                            week_time < customer_earliest_time[customer_nickname]):
                            customer_earliest_time[customer_nickname] = week_time
            except Exception as e:
                print(f"解析记录时出错: {e}")
                continue
        # 按月统计
        from collections import Counter
        from datetime import datetime
        month_counter = Counter()
        for week_time in customer_earliest_time.values():
            try:
                # 只取年月，兼容有时间的情况
                date_str = str(week_time).split(' ')[0]
                month = datetime.strptime(date_str, "%Y-%m-%d").strftime("%Y-%m")
                month_counter[month] += 1
            except Exception as e:
                print(f"解析日期时出错: {e}")
                continue
        # 按时间倒序排列
        result = [
            {"month": month, "value": month_counter[month]} 
            for month in sorted(month_counter.keys(), reverse=True)
        ]
        return jsonify(result)
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    finally:
        cursor.close()
        conn.close()

# 按月份获取当月新增客户详情
@app.route('/api/customers/by-month', methods=['GET'])
def get_customers_by_month():
    month = request.args.get('month')  # 格式: YYYY-MM
    if not month:
        return jsonify({'error': '缺少月份参数'}), 400
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    try:
        query = """
        SELECT 
            id,
            nickname,
            customer_nickname,
            ai_judgment,
            ai_tags,
            ai_customer_profile,
            ai_communication_quality,
            chat_history,
            week_start_time,
            time_span
        FROM 
            chat_history 
        WHERE 
            ai_judgment IS NOT NULL
            AND week_start_time IS NOT NULL
        """
        cursor.execute(query)
        records = cursor.fetchall()
        # 统计每个客户的最早week_start_time
        customer_earliest = {}
        customer_record = {}
        for record in records:
            try:
                if record['ai_judgment']:
                    judgment = json.loads(record['ai_judgment'])
                    if judgment.get('判断结果') == "核心业务流程分析":
                        customer_nickname = record['customer_nickname'] or record['nickname']
                        week_time = record['week_start_time']
                        if not week_time:
                            continue
                        week_time = str(week_time)  # 兼容datetime和str
                        if (customer_nickname not in customer_earliest or 
                            week_time < customer_earliest[customer_nickname]):
                            customer_earliest[customer_nickname] = week_time
                            customer_record[customer_nickname] = record
            except Exception as e:
                print(f"解析记录时出错: {e}")
                continue
        # 只保留本月的客户
        from datetime import datetime
        filtered = []
        for nickname, week_time in customer_earliest.items():
            try:
                date_str = str(week_time).split(' ')[0]
                month_str = datetime.strptime(date_str, "%Y-%m-%d").strftime("%Y-%m")
                if month_str == month:
                    # 处理详情结构
                    customer = process_customer_data(customer_record[nickname], 0)
                    filtered.append(customer)
            except Exception as e:
                print(f"解析日期时出错: {e}")
                continue
        # 添加序号
        for idx, customer in enumerate(filtered, 1):
            customer['index'] = idx
        return jsonify(filtered)
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    finally:
        cursor.close()
        conn.close()

if __name__ == '__main__':
    app.run(debug=True, port=5005) 